const Router = require('koa-router')
const router = new Router()
const auth = require('../middlewares/auth')
const Post = require('../models/Post')
const PostTag = require('../models/PostTag')
const {Op, fn, col} = require('sequelize')
const {literal} = require('../config/db')
const Users = require('../models/Users')
const {postListAdmin} = require('../attributes/post')
const validate = require('../utils/verify')
const generateSummary = require('../utils/generateSummary')
const dayjs = require('dayjs')
const {buildYearlyTree} = require('../services/postService')
const Categorys = require('../models/Categorys')
const redis = require('../config/redis')
const ArticleSubscription = require('../models/ArticleSubscription')
const Views = require('../models/Views')
const {sendMail} = require('../config/email')
const Setting = require('../models/Setting')
const {NotFoundError, ApiError} = require('../utils/customError')
require('dotenv').config()

router.prefix('/post')

// 管理端获取文章列表
router['get']('/admin/list', async (ctx) => {
  const {page = 1, pageSize = 10, key = '', category, status} = ctx.query
  const dateRange1 = ctx.query['dateRange[]']
  const offset = (Number(page) - 1) * Number(pageSize)
  let where = {}
  if (key) {
    where.title = {
      [Op.like]: `%${key}%`,
    }
  }
  if (category && !isNaN(Number(category)) && category !== '0') {
    where.categoryId = Number(category)
  }
  if (status) {
    where.status = status
  }
  if (dateRange1 && Array.isArray(dateRange1) && dateRange1.length === 2) {
    const [startDate, endDate] = dateRange1
    where.createdAt = {
      [Op.and]: [
        {[Op.gte]: dayjs(startDate).startOf('day').toDate()},
        {[Op.lte]: dayjs(endDate).endOf('day').toDate()},
      ],
    }
  }

  const result = await Post.findAndCountAll({
    attributes: [
      ...postListAdmin, // 新建分类名称字段
      [literal(`DATE_FORMAT(posts.createdAt, '%Y-%m-%d %H:%i:%s')`), 'createdAt'],
      [literal('(SELECT name FROM categorys WHERE categorys.id = posts.categoryId)'), 'category'],
      [literal('(SELECT COUNT(*) FROM views WHERE views.postId = posts.id)'), 'views']
    ],
    include: [
      {
        model: Users,
        as: 'users',
        attributes: ['id', 'username', 'avatar'],
        required: true, // 相当于 INNER JOIN
      },
    ],
    where,
    order: [['createdAt', 'DESC']],
    group: ['posts.id'],
    limit: Number(pageSize),
    offset: offset,
  })
  return (ctx.body = {
    code: 200,
    data: {
      count: result.count.length,
      rows: result.rows,
    },
  })
})

// 获取文章列表
router.get('/list', async (ctx) => {
  const params = ctx.query
  const {requesttype} = ctx.request.header
  let where = {}
  if (params.key) {
    where.title = {
      [Op.like]: `%${params.key}%`,
    }
  }
  // if (requesttype !== 'admin') {
  //   where.status = {
  //     [Op.eq]: 'published',
  //   }
  // } else {
  //   if (Array.isArray(params['dateRange[]']) && params['dateRange[]'].length === 2) {
  //     const [startDate, endDate] = params['dateRange[]']
  //     where.createdAt = {
  //       [Op.and]: [
  //         { [Op.gte]: dayjs(startDate).startOf('day').toDate() },
  //         { [Op.lte]: dayjs(endDate).endOf('day').toDate() },
  //       ],
  //     }
  //   }
  //   if (!validate.isEmpty(params.status)) {
  //     where.status = params.status
  //   }
  //   if (!validate.isEmpty(params.category)) {
  //     where.categoryId = params.category
  //   }
  // }
  // if (params.page && params.pageSize && !params.key && !params.category && !params['dateRange[]']) {
  //   const offset = (parseInt(params.page) - 1) * parseInt(params.pageSize)
  //   const postIds = await Post.findAll({
  //     attributes: ['id'],
  //     group: 'id',
  //     limit: Number.parseInt(params.pageSize, 10), // 使用 Number.parseInt 确保有效数字
  //     offset: offset,
  //     raw: true,
  //   })
  //   where = {
  //     id: postIds.length ? postIds.map((item) => item.id) : [],
  //     ...where,
  //   }
  // }
  //
  // const result = await Post.findAndCountAll({
  //   attributes: [
  //     ...postListAdmin, // 新建分类名称字段
  //     [literal(`DATE_FORMAT(posts.createdAt, '%Y-%m-%d %H:%i:%s')`), 'createdAt'],
  //     [literal('(SELECT name FROM categorys WHERE categorys.id = posts.categoryId)'), 'category'],
  //     [literal('(SELECT COUNT(*) FROM views WHERE views.postId = posts.id)'), 'views'],
  //     [
  //       literal('(SELECT color FROM categorys WHERE categorys.id = posts.categoryId)'),
  //       'categoryColor',
  //     ],
  //   ],
  //   include: [
  //     {
  //       model: Users,
  //       as: 'users',
  //       attributes: ['id', 'username', 'avatar'],
  //       required: true, // 相当于 INNER JOIN
  //     },
  //   ],
  //   where,
  //   order: [['createdAt', 'DESC']],
  //   group: ['posts.id'], // 按标签ID分组
  // })

  // // 查询文章总数
  // delete where.id
  // const count = await Post.count({
  //   where,
  // })
  // ctx.body = {
  //   code: 200,
  //   data: {
  //     count: count,
  //     rows: result.rows,
  //   },
  // }

})
// 创建文章
router['post']('/create', auth, async (ctx) => {
  const body = ctx.request.body
  const user = ctx.state.user
  if (!body.title || !body.content || !body.categoryId) {
    return (ctx.body = {
      code: 422,
      message: '参数校验失败',
    })
  }

  // 计算文章字数
  body.wordCount = body.content.replace(/<[^>]*>/g, '').length
  // 如果没有摘要,则自动提取生成
  if (validate.isEmpty(body.excerpt)) {
    // 提取文章摘要
    body.excerpt = generateSummary(body.content)
  }
  const result = await Post.create({...body, userId: user.id})
  if (body.tags) {
    for (const item of body.tags) {
      await PostTag.create({postId: result.id, tagId: item})
    }
  }
  if (!result) {
    ctx.body = {
      code: 500,
      message: '创建失败,请稍后重试',
    }
    return
  }
  // 创建成功查询订阅的邮箱
  const emails = await ArticleSubscription.findAll()
  // 查询配置信息
  const config = await Setting.findOne({
    where: {
      id: 1,
    },
  })
  for (const item of emails) {
    const email = item.email
    const subject = '新文章发布提醒'
    const html = `<p>您好，${email}，「${config.websiteName}」有新的文章发布，请及时查看。</p>`
    await sendMail(email, subject, html)
  }
  ctx.body = {
    code: 200,
    data: true,
  }
})
// 修改文章状态
router['get']('/updateStatus', auth, async (ctx) => {
  const {id, status} = ctx.query
  if (!id || !status) {
    return (ctx.body = {
      code: 422,
      data: {
        message: '参数校验失败',
      },
    })
  }
  const result = await Post.update({status}, {where: {id}})
  if (!result) {
    throw new Error('修改失败,请稍后重试')
  }
  ctx.body = {
    code: 200,
    data: true,
  }
})
// id 获取文章
router['get']('/get/:id', auth, async (ctx) => {
  const {id} = ctx.params
  const {requesttype} = ctx.request.header
  if (!id) {
    return (ctx.body = {
      code: 422,
      data: {
        message: '参数校验失败',
      },
    })
  }
  const postList = postListAdmin
  if (requesttype === 'admin') {
    postList.push('visibility')
    postList.push('password')
  }
  const result = await Post.findByPk(id, {
    attributes: [
      ...postList,
      'metaTitle',
      'metaDescription',
      'keywords',
      'allowComment', // 新建分类名称字段
      [literal(`DATE_FORMAT(posts.createdAt, '%Y-%m-%d %H:%i:%s')`), 'createdAt'],
      [
        literal(
          '(SELECT name FROM categorys WHERE categorys.id = posts.categoryId and categorys.deletedAt IS NULL)',
        ),
        'category',
      ],
      // 获取标签id
        [
          literal(`(
        SELECT JSON_ARRAYAGG(tags.id)
        FROM tags
        INNER JOIN post_tags ON tags.id = post_tags.tagId
        WHERE post_tags.postId = posts.id
      )`),
          'tags',
        ],
      // 获取标签名称
      [
        literal(`(
      SELECT JSON_ARRAYAGG(tags.name)
      FROM tags 
      INNER JOIN post_tags ON tags.id = post_tags.tagId 
      WHERE post_tags.postId = posts.id
    )`),
        'tagsName',
      ],
    ], // 直接返回JSON数组[]
    include: [
      {
        model: Users,
        as: 'users',
        attributes: ['id', 'username', 'avatar'],
        required: true, // 相当于 INNER JOIN
      },
    ],
  })
  if (!result) {
    return (ctx.body = {
      code: 404,
      data: {
        message: '文章不存在',
      },
    })
  }
  ctx.body = {
    code: 200,
    data: result,
  }
})
// 根据id 获取文章
router.get('/getfront/:id/:password', async (ctx) => {
  const {id, password} = ctx.params
  const ip = String(ctx.ip)

  // 设置每个ip24小时访问量限制
  const maxRequests = 5 // 每篇文章24小时最大请求数
  const windowMs = 24 * 60 * 60 * 1000 // 24小时
  // Redis键名
  const redisKey = `article_rate_limit:${id}:${ip}`
  // 获取当前计数
  let currentCount = await redis.get(redisKey)
  if (currentCount === null) {
    // 新IP访问该文章，设置过期时间并初始化计数
    await redis.multi().set(redisKey, 1, 'PX', windowMs).exec()
    currentCount = 1
  } else {
    currentCount = await redis.incr(redisKey)
  }
  if (currentCount <= maxRequests) {
    // 插入数据库
    await Views.create({postId: id})
  } else {
    console.log(`${ip}访问超出限制`)
  }
  // 先判断文章是否是加密
  const result = await Post.findByPk(id, {
    attributes: [
      'id',
      'title',
      'coverImage',
      'categoryId',
      'content',
      'status',
      'createdAt',
      'likes',
      'password',
      'keywords',
      'allowComment',
      'visibility',
      // 新建分类名称字段
      [literal('(SELECT name FROM categorys WHERE categorys.id = posts.categoryId)'), 'category'], // 查询文章的浏览量
      [literal('(SELECT COUNT(*) FROM views WHERE views.postId = posts.id)'), 'views'],
    ],
    include: [
      {
        model: Users,
        as: 'users',
        attributes: ['username', 'avatar'],
        required: true, // 相当于 INNER JOIN
      },
    ],
  })
  if (result && result.dataValues.visibility === 'password') {
    if (password !== result.dataValues.password) {
      return (ctx.body = {
        code: 20111,
        data: {
          code: 20111,
          message: '密码错误',
        },
      })
    }
  }
  if (!result) {
    throw new NotFoundError('文章不存在')
  }
  ctx.body = {
    code: 200,
    data: {
      code: 200,
      data: result,
      message: '获取成功',
    },
  }
})
// 保存文章数据
router['post']('/update', auth, async (ctx) => {
  const body = ctx.request.body
  if (!body.id) {
    return (ctx.body = {
      code: 422,
      message: {
        message: '参数校验失败',
      },
    })
  }
  const result = await Post.update(body, {where: {id: body.id}})
  // 修改post_tags关联的文章标签
  if (body.tags) {
    await PostTag.destroy({where: {postId: body.id}})
    for (const item of body.tags) {
      await PostTag.create({postId: body.id, tagId: item})
    }
  }
  if (!result) {
    return (ctx.body = {
      code: 500,
      data: '修改失败,请稍后重试',
    })
  }
  ctx.body = {
    code: 200,
    data: true,
  }
})
// 删除文章
router['post']('/delete', auth, async (ctx) => {
  const {id} = ctx.request.body
  if (!id) {
    return (ctx.body = {
      code: 422,
      data: {
        message: '参数校验失败',
      },
    })
  }
  const result = await Post.destroy({where: {id}})
  // 删除post_tags关联的文章标签
  await PostTag.destroy({where: {postId: id}})
  if (!result) {
    throw new Error('删除失败,请稍后重试')
  }
  ctx.body = {
    code: 200,
    data: true,
  }
})
// 批量删除文章
router['post']('/batchDelete', auth, async (ctx) => {
  const {ids} = ctx.request.body
  if (!ids) {
    return (ctx.body = {
      code: 422,
      message: '参数校验失败',
    })
  }
  const result = await Post.destroy({where: {id: ids}})
  if (!result) {
    throw new Error('删除失败,请稍后重试')
  }
  ctx.body = {
    code: 200,
    data: true,
  }
})
// 最近文章
router['get']('/recent', async (ctx) => {
  const recentArticles = await Post.findAll({
    order: [['createdAt', 'DESC']],
    attributes: [
      'id',
      'title',
      'createdAt',
      'coverImage',
      [
        literal(`(
          SELECT COUNT(*)
          FROM views AS view
          WHERE view.postId = posts.id
        )`),
        'views',
      ],
      [
        literal(`(
          SELECT COUNT(*)
          FROM comments AS comment
          WHERE comment.postId = posts.id
        )`),
        'comments',
      ],
      [
        literal(`(
          SELECT username
          FROM users AS user
          WHERE user.id = posts.userId
        )`),
        'author',
      ], // 直接内联获取用户名
      [
        literal(`(
           SELECT name
           FROM categorys AS category
           WHERE category.id = posts.categoryId
         )`),
        'category',
      ],
    ],
    limit: 5,
  })
  ctx.body = {
    code: 200,
    data: recentArticles,
  }
})
// 获取文章列表前端展示
router['get']('/getArticle', async (ctx) => {
  const {page = '1', pageSize = '10'} = ctx.query
  const offset = (parseInt(page) - 1) * parseInt(pageSize)

  const result = await Post.findAndCountAll({
    attributes: [
      'id',
      'title',
      'coverImage',
      'excerpt',
      'likes',
      [literal(`DATE_FORMAT(posts.createdAt, '%Y-%m-%d %H:%i:%s')`), 'createdAt'],
      [
        literal(`(
      SELECT COUNT(*)
      FROM views
      WHERE views.postId = posts.id
    )`),
        'views',
      ],
      [
        literal(`(
      SELECT GROUP_CONCAT(tags.name SEPARATOR ',')
      FROM post_tags
      JOIN tags ON post_tags.tagId = tags.id
      WHERE post_tags.postId = posts.id
    )`),
        'tags',
      ],
      [
        literal(`(
      SELECT name
      FROM categorys AS category
      WHERE category.id = posts.categoryId
      and category.deletedAt IS NULL
    )`),
        'categoryName',
      ],
    ],
    where: {
      status: 'published', // 查询分类没被删除的文章
    },
    include: [
      {
        model: Categorys,
        as: 'categorys',
        attributes: [], // 不返回分类数据，只用于过滤
        required: false, // 使用LEFT JOIN
      },
    ],
    order: [['createdAt', 'DESC']],
    limit: parseInt(pageSize),
    offset: offset, // 注意：使用 GROUP BY 时需要关闭 Sequelize 的计数优化
    group: ['posts.id'],
    subQuery: false, // 防止分页和分组冲突
  })
  // 格式化数据
  const formattedPosts = result.rows.map((post) => ({
    id: post.id,
    title: post.title,
    coverImage: post.coverImage,
    excerpt: post.excerpt,
    likes: post.likes,
    tags: post.tags,
    categoryName: post.categoryName,
    createdAt: post.createdAt,
    ...post.dataValues,
    views: post.views, // 直接使用 Sequelize 计算的 views 数量
    // tags: post.tags ? post.tags.split(',') : [] // 分割标签字符串
  }))
  ctx.body = {
    code: 200,
    data: {
      count: Array(...result.count).length,
      rows: formattedPosts,
    },
  }
})
// 客户端搜索文章
router['get']('/search', async (ctx) => {
  const {page = '1', pageSize = '10', keyword, time = '', category = '', sort = ''} = ctx.query
  if (!keyword) {
    return (ctx.body = {
      code: 422,
      data: {
        message: '参数校验失败',
      },
    })
  }

  const offset = (parseInt(page) - 1) * parseInt(pageSize)
  let where = {}
  let order = []
  if (keyword) {
    where = {
      [Op.or]: [
        {title: {[Op.like]: `%${keyword}%`}},
        {excerpt: {[Op.like]: `%${keyword}%`}},
        {content: {[Op.like]: `%${keyword}%`}},
      ],
    }
  }
  if (time) {
    // 获取最近1周发布的文章
    where = {
      ...where,
      createdAt: {
        [Op.gte]: dayjs().subtract(Number(time), 'days').startOf('day').toDate('YYYY-MM-DD'),
      },
    }
    order = [['createdAt', 'DESC']] // 按发布时间降序排列
  }
  if (category) {
    where = {
      ...where,
      categoryId: category,
    }
  }
  if (sort === 'popular') {
    // 排序
    order = [
      [
        literal(`(
        SELECT COUNT(*)
        FROM views
        WHERE views.postId = posts.id
      )`),
        'DESC',
      ],
    ]
  }
  if (sort === 'latest') {
    // 按照最新发布文章排序
    order = [['createdAt', 'DESC']]
  }
  // 进行查询
  const result = await Post.findAndCountAll({
    attributes: [
      'id',
      'title',
      'coverImage',
      'excerpt',
      'createdAt',
      [
        literal(`(
        SELECT COUNT(*)
        FROM views
        WHERE views.postId = posts.id
      )`),
        'views',
      ],
      [
        literal(`(
        SELECT JSON_ARRAYAGG(tags.name)
        FROM post_tags
        JOIN tags ON post_tags.tagId = tags.id
        WHERE post_tags.postId = posts.id
      )`),
        'tags',
      ],
      [
        literal(`(
        SELECT name
        FROM categorys AS category
        WHERE category.id = posts.categoryId
      )`),
        'categoryName',
      ],
    ],
    where: {
      ...where,
      status: 'published',
    },
    order,
    limit: parseInt(pageSize),
    offset: offset, // 注意：使用 GROUP BY 时需要关闭 Sequelize 的计数优化
    group: ['posts.id'],
    subQuery: false,
  })
  ctx.body = {
    code: 200,
    data: {
      count: Array(...result.count).length,
      rows: result.rows,
    },
  }
})
// 查询归档时间线
router['get']('/archive', async (ctx) => {
  // 查询所有创建日期
  const monthlyStats = await Post.findAll({
    attributes: [
      [fn('YEAR', col('createdAt')), 'year'],
      [fn('MONTH', col('createdAt')), 'month'],
      [fn('COUNT', col('id')), 'post_count'],
    ],
    where: {
      deletedAt: null,
      status: 'published',
    },
    group: [fn('YEAR', col('createdAt')), fn('MONTH', col('createdAt'))],
    order: [
      [fn('YEAR', col('createdAt')), 'DESC'],
      [fn('MONTH', col('createdAt')), 'DESC'],
    ],
    raw: true,
  })
  ctx.body = {
    code: 200,
    data: {
      date: buildYearlyTree(monthlyStats),
    },
  }
})
// 获取时间线文章
router['get']('/timeline', async (ctx) => {
  const {page, pageSize, sortBy = 'default', filterTag = '', date = ''} = ctx.query
  // 参数校验（别他妈传些乱七八糟的）
  const parsedPage = Math.max(1, parseInt(page)) || 1
  const parsedPageSize = Math.max(1, parseInt(pageSize)) || 10

  let where = {}
  const order = []
  // 标签过滤
  if (filterTag) {
    const tagId = parseInt(filterTag) // 确保是数字
    where['$post_tags.tagId$'] = tagId // 关联查询条件
  }
  // 日期查询（精确到年月，别瞎几把传）
  if (date) {
    if (date.includes('-')) {
      // 月份查询
      const [year, month] = date.split('-').map(Number)
      const startDate = dayjs(`${year}-${month}-01`).startOf('month').toDate()
      const endDate = dayjs(startDate).endOf('month').toDate()
      where.createdAt = {[Op.between]: [startDate, endDate]}
    } else {
      // 年份查询
      const year = parseInt(date)
      const startDate = dayjs(`${year}-01-01`).startOf('year').toDate()
      const endDate = dayjs(startDate).endOf('year').toDate()
      where.createdAt = {[Op.between]: [startDate, endDate]}
    }
  }

  // 排序逻辑
  if (sortBy === 'popular') {
    order.push([literal('(SELECT COUNT(*) FROM views WHERE views.postId = posts.id)'), 'DESC'])
  } else if (sortBy === 'date') {
    order.push(['createdAt', 'DESC'])
  }
  // 查询数据（优化分组计数）
  const result = await Post.findAndCountAll({
    attributes: [
      'id',
      'title',
      'likes',
      'coverImage',
      'excerpt',
      [literal(`DATE_FORMAT(posts.createdAt, '%Y-%m-%d %H:%i:%s')`), 'createdAt'],
      [literal('(SELECT COUNT(*) FROM views WHERE views.postId = posts.id)'), 'views'],
      [
        literal(
          'COALESCE((SELECT JSON_ARRAYAGG(tags.name) FROM post_tags JOIN tags ON post_tags.tagId = tags.id WHERE post_tags.postId = posts.id), JSON_ARRAY())',
        ),
        'tags',
      ],
    ],
    // 关联post_tags
    include: [
      {
        model: PostTag,
        as: 'post_tags',
        attributes: [],
        where: filterTag ? {tagId: parseInt(filterTag)} : undefined,
        required: !!filterTag,
      },
    ],
    limit: parsedPageSize,
    offset: (parsedPage - 1) * parsedPageSize,
    order,
    where: {
      ...where,
      status: 'published',
    },
    subQuery: false, // 避免分页问题
    distinct: true, // 重要！避免分页计数错误
  })

  // 返回结构（别他妈让前端猜数据）
  ctx.body = {
    code: 200,
    data: {
      count: result.count, // 直接取分组后的数量
      rows: result.rows,
    },
  }
})
// 文章点赞
router.post('/like', async (ctx) => {
  const {post_id} = ctx.request.body
  const ip = ctx.ip.replace(/\./g, '_') // 简单处理IP
  const ipKey = `ip:liked:${post_id}:${ip}`

  if (!post_id) {
    ctx.body = {
      code: 400,
      data: {
        status: false,
        message: '文章ID不能为空',
      },
    }
    return
  }
  if (!(await Post.findOne({where: {id: post_id}}))) {
    ctx.body = {
      code: 404,
      data: {status: false, message: '文章不存在'},
    }
    return
  }
  if (await redis.exists(ipKey)) {
    ctx.body = {
      code: 422,
      data: {
        status: false,
        message: '24小时内只能赞一次',
      },
    }
    return
  }
  // 对应文章点赞+1
  await Post.increment('likes', {where: {id: post_id}})
  await redis
    .pipeline()
    .incr(`post:likes:${post_id}`)
    .setex(ipKey, 86400, '1') // 24小时过期
    .exec()

  ctx.body = {
    code: 200,
    data: {
      status: true,
      message: '点赞成功',
    },
  }
})
// 文章订阅接口
router.post('/subscribe', async (ctx) => {
  const {email} = ctx.request.body
  // 订阅频率限制（1分钟/邮箱）
  const SUBSCRIPTION_LIMIT = {
    key: 'sub_limit:{email}',
    ttl: 60,
  }
  // 验证码存储（可选）
  const VERIFY_CODE = {
    key: 'sub_code:{email}',
    ttl: 300,
  }
  // 1. 校验邮箱格式
  if (!/^\w+@\w+\.\w+$/.test(email)) {
    ctx.throw(400, '邮箱格式错误')
    return
  }
  // 2. 限流检查
  const limitKey = SUBSCRIPTION_LIMIT.key.replace('{email}', email)
  if (await redis.exists(limitKey)) {
    ctx.throw(429, '操作过于频繁')
    return
  }
  // 是否已经订阅
  if (await ArticleSubscription.findOne({where: {email}})) {
    ctx.body = {
      code: 4000,
      data: {
        status: false,
        message: '您已经订阅过了',
      },
    }
    return
  }
  // 3. 持久化记录
  try {
    const config = await Setting.findOne({where: {id: 1}})
    const a = await sendMail(email, `【${config.websiteName}】订阅`, `感谢您的订阅~`)
    if (a === 'true') {
      await ArticleSubscription.upsert({
        email: email,
        unsubscribe_token: fn('MD5', fn('RAND')),
      })
      // 设置限流锁
      await redis.setex(limitKey, SUBSCRIPTION_LIMIT.ttl, '1')
      ctx.body = {
        code: 200,
        data: {
          status: true,
          message: '订阅成功',
        },
      }
    }
  } catch (e) {
    console.error(e)
    ctx.throw(500, '订阅失败')
  }
})
// 获取热门文章
router.get('/hot', async (ctx) => {
  // 关联views获取阅读量最多的10篇文章
  const result = await Post.findAll({
    attributes: [
      'id',
      'title',
      'coverImage',
      [literal(`(SELECT COUNT(*) FROM views WHERE views.postId = posts.id)`), 'views'],
    ],
    order: [[literal('(SELECT COUNT(*) FROM views WHERE views.postId = posts.id)'), 'DESC']],
    limit: 5,
  })

  ctx.body = {
    code: 200,
    data: result,
  }
})

module.exports = router
